from flask import Blueprint, render_template, jsonify
import pymysql
from pymysql.cursors import DictCursor
from datetime import datetime, timedelta
import numpy as np

screen_bp = Blueprint('screen', __name__, url_prefix='/screen')

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'Ab_data',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}


def get_db_connection():
    return pymysql.connect(**DB_CONFIG)


def calculate_model_accuracy(connection):
    """计算各模型的准确率"""
    cursor = connection.cursor()

    # 获取所有有实际环数和预测环数的记录
    cursor.execute("""
        SELECT rings, predicted_rings_LR, predicted_rings_DT_R, predicted_rings_RF_R
        FROM new_abalone
        WHERE rings IS NOT NULL 
        AND (predicted_rings_LR IS NOT NULL 
             OR predicted_rings_DT_R IS NOT NULL 
             OR predicted_rings_RF_R IS NOT NULL)
    """)
    records = cursor.fetchall()

    if not records:
        return {
            'LR': 0,
            'DT-R': 0,
            'RF-R': 0
        }

    # 计算每个模型的准确率
    lr_errors = []
    dt_errors = []
    rf_errors = []

    for record in records:
        actual = record['rings']

        if record['predicted_rings_LR'] is not None:
            lr_errors.append(abs(record['predicted_rings_LR'] - actual))

        if record['predicted_rings_DT_R'] is not None:
            dt_errors.append(abs(record['predicted_rings_DT_R'] - actual))

        if record['predicted_rings_RF_R'] is not None:
            rf_errors.append(abs(record['predicted_rings_RF_R'] - actual))

    # 计算平均绝对误差百分比 (MAPE)
    def calculate_mape(errors, total):
        if not errors:
            return 0
        avg_error = np.mean(errors)
        return max(0, 100 - (avg_error / np.mean([r['rings'] for r in records]) * 100))

    return {
        'LR': calculate_mape(lr_errors, len(records)),
        'DT-R': calculate_mape(dt_errors, len(records)),
        'RF-R': calculate_mape(rf_errors, len(records))
    }


def get_model_usage_stats(connection):
    """获取各模型使用统计"""
    cursor = connection.cursor()

    # 计算每个模型的预测次数
    cursor.execute("""
        SELECT 
            SUM(CASE WHEN predicted_rings_LR IS NOT NULL THEN 1 ELSE 0 END) as lr_count,
            SUM(CASE WHEN predicted_rings_DT_R IS NOT NULL THEN 1 ELSE 0 END) as dt_count,
            SUM(CASE WHEN predicted_rings_RF_R IS NOT NULL THEN 1 ELSE 0 END) as rf_count
        FROM new_abalone
    """)
    counts = cursor.fetchone()

    total = counts['lr_count'] + counts['dt_count'] + counts['rf_count']

    if total == 0:
        return [
            {'name': 'LR', 'value': 0},
            {'name': 'DT-R', 'value': 0},
            {'name': 'RF-R', 'value': 0}
        ]

    return [
        {'name': 'LR', 'value': counts['lr_count']},
        {'name': 'DT-R', 'value': counts['dt_count']},
        {'name': 'RF-R', 'value': counts['rf_count']}
    ]


def get_recent_predictions(connection):
    """获取最近预测记录"""
    cursor = connection.cursor()

    # 获取最近24小时的预测记录（从三个模型中合并）
    cursor.execute("""
        (SELECT id, predicted_rings_LR as predicted_rings, 'LR' as model, prediction_time_LR as prediction_time
         FROM new_abalone
         WHERE prediction_time_LR >= %s
         ORDER BY prediction_time_LR DESC LIMIT 7)

        UNION ALL

        (SELECT id, predicted_rings_DT_R as predicted_rings, 'DT-R' as model, prediction_time_DT_R as prediction_time
         FROM new_abalone
         WHERE prediction_time_DT_R >= %s
         ORDER BY prediction_time_DT_R DESC LIMIT 7)

        UNION ALL

        (SELECT id, predicted_rings_RF_R as predicted_rings, 'RF-R' as model, prediction_time_RF_R as prediction_time
         FROM new_abalone
         WHERE prediction_time_RF_R >= %s
         ORDER BY prediction_time_RF_R DESC LIMIT 7)

        ORDER BY prediction_time DESC
        LIMIT 20
    """, (datetime.now() - timedelta(hours=24),
          datetime.now() - timedelta(hours=24),
          datetime.now() - timedelta(hours=24)))

    predictions = cursor.fetchall()

    # 格式化时间
    formatted_predictions = []
    for pred in predictions:
        formatted_predictions.append({
            'id': pred['id'],
            'predicted_rings': pred['predicted_rings'],
            'prediction_model': pred['model'],
            'prediction_time': pred['prediction_time'].strftime('%Y-%m-%d %H:%M:%S') if pred[
                'prediction_time'] else 'N/A'
        })

    return formatted_predictions


@screen_bp.route('/')
def screen():
    """大屏展示页面"""
    return render_template('screen.html')


@screen_bp.route('/data')
def get_screen_data():
    """获取大屏展示数据"""
    connection = get_db_connection()
    try:
        # 1. 计算模型准确率
        model_accuracies = calculate_model_accuracy(connection)

        # 2. 获取模型使用统计
        model_stats = get_model_usage_stats(connection)

        # 3. 获取最近预测记录
        prediction_logs = get_recent_predictions(connection)

    finally:
        connection.close()

    return jsonify({
        'success': True,
        'modelAccuracies': model_accuracies,
        'predictionLogs': prediction_logs,
        'modelStats': model_stats
    })